import json

import numpy as np
import pandas as pd
import random
import mysql.connector
import hashlib

from matplotlib import pyplot as plt
from matplotlib.font_manager import FontProperties

font = FontProperties(fname='D:/fonts/simhei.ttf')  # Windows系统路径

# MySQL 数据库连接配置
config = {
    'user': 'hsh',
    'password': '8198241817',
    'host': 'localhost',
    'database': 'yuntianhui',
    'raise_on_warnings': True
}

def connect_to_database(config):
    try:
        conn = mysql.connector.connect(**config)
        if conn.is_connected():
            print('Connected to MySQL database')
            return conn, conn.cursor()
    except mysql.connector.Error as e:
        print(f"Error connecting to MySQL database: {e}")
        return None, None

def md5_encrypt(text):
    """
    使用MD5算法加密输入的文本

    参数:
    text (str): 需要加密的文本

    返回:
    str: 加密后的MD5哈希值
    """
    # 创建一个MD5哈希对象
    md5_hash = hashlib.md5()

    # 更新哈希对象，必须以字节形式提供输入
    md5_hash.update(text.encode('utf-8'))

    # 获取十六进制的哈希值
    return md5_hash.hexdigest()


def get_userId():
    try:
        # 连接到MySQL数据库
        conn = mysql.connector.connect(**config)
        cursor = conn.cursor()
        # 执行查询语句
        query = "SELECT uid FROM user"
        cursor.execute(query)

        # 获取所有用户ID
        user_ids = [row[0] for row in cursor.fetchall()]
        print(user_ids)
        print(len(user_ids))
        return user_ids

    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        # 关闭数据库连接
        if conn.is_connected():
            cursor.close()
            conn.close()
            print("MySQL connection is closed")


def get_postsId():
    try:
        conn = mysql.connector.connect(**config)
        cursor = conn.cursor()
        # 执行查询语句
        query = "SELECT pid FROM posts"
        cursor.execute(query)

        # 获取所有用户ID
        posts_ids = cursor.fetchall()
        print(posts_ids)
        print(len(posts_ids))
        return posts_ids

    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        # 关闭数据库连接
        if conn.is_connected():
            cursor.close()
            conn.close()
            print("MySQL connection is closed")
def browingsinghistory_data_make():
    # 定义生成的历史记录数量
    num_records = 50

def userInfo_make():
    # 指定的前缀列表
    prefix_list = [
        "133", "149", "153", "173", "177", "180", "181", "189", "199", "130",
        "131", "132", "145", "155", "156", "166", "171", "175", "176", "185",
        "186", "166", "134", "135", "136", "137", "138", "139", "147", "150",
        "151", "152", "157", "158", "159", "172", "178", "182", "183", "184",
        "187", "188", "198"
    ]

    population_data = {
        "全国": 1441497378,
        "北京": 21893095,
        "天津": 13866009,
        "河北": 74610235,
        "山西": 34915616,
        "内蒙古": 24049155,
        "辽宁": 42591407,
        "吉林": 24073453,
        "黑龙江": 31850088,
        "上海": 24870895,
        "江苏": 84748016,
        "浙江": 64567588,
        "安徽": 61027171,
        "福建": 41540086,
        "江西": 45188635,
        "山东": 101527453,
        "河南": 99365519,
        "湖北": 57752557,
        "湖南": 66444864,
        "广东": 126012510,
        "广西": 50126804,
        "海南": 10081232,
        "重庆": 32054159,
        "四川": 83674866,
        "贵州": 38562148,
        "云南": 47209277,
        "西藏": 3648100,
        "陕西": 39528999,
        "甘肃": 25019831,
        "青海": 5923957,
        "宁夏": 7202654,
        "新疆": 25852345,
        "香港": 7474200,
        "澳门": 683218,
        "台湾": 23561236
    }

    # 总人口
    total_population = population_data["全国"]

    # 计算各地区人口占比
    population_percentage = {region: population / total_population for region, population in population_data.items() if
                             region != "全国"}

    def make_phone(count):
        """
            生成以指定数字开头的11位电话号码

            参数:
            prefix_list (list): 包含前缀的列表
            count (int): 生成电话号码的数量

            返回:
            list: 生成的电话号码列表
            """

        phone_numbers = []

        for _ in range(count):
            prefix = random.choice(prefix_list)
            suffix = ''.join(random.choices('0123456789', k=11 - len(prefix)))
            phone_number = prefix + suffix
            phone_numbers.append(phone_number)


        print(phone_numbers)
        return phone_numbers

    def make_area(count):
        """
            根据人口占比生成随机地区

            参数:
            population_percentage (dict): 各地区人口占比字典
            count (int): 生成地区数量

            返回:
            list: 生成的随机地区列表
            """
        regions = list(population_percentage.keys())
        probabilities = list(population_percentage.values())
        print(random.choices(regions, weights=probabilities, k=count))
        return random.choices(regions, weights=probabilities, k=count)

    def make_age(count):
        # 设定随机种子，确保每次运行生成的随机数一致
        np.random.seed(0)

        # 总用户数假设为 count
        total_users = count

        # 设定目标均值和标准差
        target_mu = 23  # 目标均值
        target_sigma = 12  # 目标标准差

        # 根据目标均值和标准差计算新的正态分布参数
        mu = target_mu  # 设定均值为目标均值
        sigma = target_sigma  # 设定标准差为目标标准差

        # 生成正态分布的随机数据，限制在14岁到60岁之间
        ages = np.random.normal(mu, sigma, total_users)
        ages =ages[(ages >= 14) & (ages <= 60)]  # 限制在14岁到60岁之间
        print(type(ages))
        return ages

        # # 绘制直方图
        # plt.figure(figsize=(10, 6))
        # plt.hist(ages, bins=range(14, 61), color='skyblue', edgecolor='black', alpha=0.7)
        # plt.xlabel('年龄', fontproperties=font)
        # plt.ylabel('用户数量', fontproperties=font)
        # plt.title('用户年龄分布', fontproperties=font)
        # plt.xticks(range(14, 61, 2), fontproperties=font)
        # plt.yticks(fontproperties=font)
        # plt.tight_layout()
        # plt.show()
        #
        # # 打印统计信息
        # print(f"生成的用户数量: {len(ages)}")
        # print(f"实际均值: {np.mean(ages):.2f}")
        # print(f"实际标准差: {np.std(ages):.2f}")

    # make_phone(100)
    # make_area(100)
    # make_age(count=1000000)
    user_infos = []
    userIds = get_userId()
    id_sum = len(userIds)
    # print(id_sum)
    areas = make_area(id_sum)
    print(int(id_sum/0.8))
    phone_numbers = make_phone(id_sum)
    ages = make_age(int(id_sum/0.7))
    print(ages.dtype)
    ages = list(ages)
    ages_int = [int(x) for x in ages]
    print(type(ages_int))
    print(type(ages_int[0]))
    print(len(ages))
    data_list = []
    for i in range(id_sum):
        data= {
            'user_id': userIds[i],
            'phone': phone_numbers[i],
            'area': areas[i],
            'age': ages[i],
            'password':md5_encrypt('123456')
        }
        data_list.append(data)

    def update_database(cursor,user_id, area, age,phone,password):
        try:

            print(conn.is_connected())
            # 执行更新语句
            update_query = "UPDATE user SET area = %s, age = %s , phone = %s , prohibition = 0 ,password = %s WHERE uid = %s"
            cursor.execute(update_query, (area, age, phone, password, user_id))
            conn.commit()

            print(f"Successfully updated user {user_id} in the database.")

        except mysql.connector.Error as err:
            print(f"Error updating user {user_id}: {err}")

    conn = mysql.connector.connect(**config)
    cursor = conn.cursor()

    for data in data_list:
        user_id = data['user_id']
        phone = data['phone']
        area = data['area']
        age = data['age']
        password = data['password']
        # 调用更新数据库的函数
        update_database(cursor, user_id, area, age,phone,password)

    # 打印示例
    print(data_list)  # 打印用户ID为1的数据
    cursor.close()
    conn.close()
    print("MySQL connection is closed")



# 生成大量的随机地区
    def test1():
        sample_count = 1000000
        random_regions = make_area( sample_count)

        # 统计每个地区出现的次数
        region_counts = {region: random_regions.count(region) for region in population_percentage.keys()}

        # 打印实际生成的比例和预期比例
        print("Region\t\tGenerated Ratio\t\tExpected Ratio")
        for region in population_percentage.keys():
            generated_ratio = region_counts[region] / sample_count
            expected_ratio = population_percentage[region]
            print(f"{region}\t\t{generated_ratio:.6f}\t\t{expected_ratio:.6f}")

        # 可视化结果
        regions = list(population_percentage.keys())
        generated_ratios = [region_counts[region] / sample_count for region in regions]
        expected_ratios = [population_percentage[region] for region in regions]




        # 绘制条形图
        x = range(len(regions))
        plt.figure(figsize=(14, 7))
        plt.bar(x, generated_ratios, width=0.4, label='Generated Ratios', align='center')
        plt.bar(x, expected_ratios, width=0.4, label='Expected Ratios', align='edge')
        plt.xlabel('Regions', fontproperties=font)
        plt.ylabel('Ratio', fontproperties=font)
        plt.title('Generated vs Expected Population Ratios', fontproperties=font)
        plt.xticks(x, regions, rotation='vertical', fontproperties=font)
        plt.legend(prop=font)
        plt.tight_layout()
        plt.show()
    def test2():
        # 排除全国总人口数据，确保只绘制各省人口数据
        regions = list(population_data.keys())
        regions = regions[1:]
        populations = list(population_data.values())
        populations = populations[1:]

        # 绘制柱状图
        plt.figure(figsize=(14, 7))
        plt.bar(regions, populations, color='skyblue')
        plt.xlabel('省份', fontproperties=font)
        plt.ylabel('人口数量', fontproperties=font)
        plt.title('中国各省人口数量', fontproperties=font)
        plt.xticks(rotation=90, fontproperties=font)
        plt.tight_layout()
        plt.show()


    def test3():
        # 总用户数假设为100
        total_users = 100

        # 各年龄段用户比例
        age_18_24 = 60.28 / 100 * total_users
        age_25_30 = 16.71 / 100 * total_users
        age_others = total_users - (age_18_24 + age_25_30)  # 剩余用户

        # 各年龄段标签和用户数
        age_labels = ['18-24岁', '25-30岁', '其他']
        user_counts = [age_18_24, age_25_30, age_others]

        # 绘制柱状图
        plt.figure(figsize=(10, 6))
        plt.bar(age_labels, user_counts, color=['skyblue', 'lightgreen', 'salmon'])
        plt.xlabel('年龄段', fontproperties=font)
        plt.ylabel('用户比例 (%)', fontproperties=font)
        plt.title('用户年龄分布', fontproperties=font)
        plt.xticks(fontproperties=font)
        plt.tight_layout()
        plt.show()

    # test1()
    # test2()
    # test3()

def recombine_categories():
    def get_new_category():
        try:
            # 建立数据库连接
            conn = mysql.connector.connect(**config)
            cursor = conn.cursor()

            if conn.is_connected():
                print("Connected to the database")

            # 执行查询语句
            select_query = "SELECT cid, type, fatherid FROM categorize"
            cursor.execute(select_query)

            # 获取查询结果
            data = cursor.fetchall()
            print(data)
            return data

        except mysql.connector.Error as err:
            print(f"Error: {err}")

        finally:
            # 关闭数据库连接
            if conn.is_connected():
                cursor.close()
                conn.close()
                print("MySQL connection is closed")


    try:
        # 建立数据库连接
        conn = mysql.connector.connect(**config)
        cursor = conn.cursor()

        if conn.is_connected():
            print("Connected to the database")

        # 更新posts表中的categorized字段
        update_query = """
        UPDATE posts p
        JOIN categorize c ON p.categoryname = c.type
        SET p.categorizeid = c.cid
        """

        cursor.execute(update_query)

        # 提交更改
        conn.commit()
        print("Posts table updated successfully")

    except mysql.connector.Error as err:
        print(f"Error: {err}")
        conn.rollback()  # 如果出现错误，回滚更改

    finally:
        # 关闭数据库连接
        if conn.is_connected():
            cursor.close()
            conn.close()
            print("MySQL connection is closed")


def get_user_categories():
    final_result = []
    try:
        # 建立数据库连接
        conn = mysql.connector.connect(**config)
        cursor = conn.cursor()
        if conn.is_connected():
            print("Connected to the database")
        # 第一步：从posts表中聚合uid和categorizeid字段，并去重
        select_temp_posts = """
        SELECT DISTINCT uid, categorizeid
        FROM posts
        """
        cursor.execute(select_temp_posts)
        temp_posts = cursor.fetchall()
        print(temp_posts)
        # 第二步：通过categorizeid连接categorize表，获取对应的id
        temp_uid_id = []
        for uid, categorizeid in temp_posts:
            select_categorize_id = """
            SELECT fatherid
            FROM categorize
            WHERE cid = %s
            """
            cursor.execute(select_categorize_id, (categorizeid,))
            result = cursor.fetchone()
            if result:
                temp_uid_id.append((uid, result[0]))
        print(temp_uid_id)
        # 第三步：将categorize表中的type加入到最终结果中

        for uid, cid in temp_uid_id:
            select_type = """
            SELECT type
            FROM categorize
            WHERE cid = %s
            """
            cursor.execute(select_type, (cid,))
            type_result = cursor.fetchone()
            if type_result:
                final_result.append((uid, cid, type_result[0]))

        # 打印结果示例
        # for row in final_result:
        #     print(row)
        print(final_result)
        return final_result

    except mysql.connector.Error as err:
        print(f"Error: {err}")

    finally:
        # 关闭数据库连接
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()
            print("MySQL connection is closed")

    # # 使用集合存储已经出现过的 (id, type) 组合
    # seen_combinations = set()
    #
    # # 使用字典存储每个 uid 对应的唯一 (id, type) 组合
    # aggregated_data = {}
    #
    # for uid, cid, ctype in final_result:
    #     if uid in aggregated_data:
    #         aggregated_data[uid].add((cid, ctype))  # 将 (cid, type) 加入集合中
    #     else:
    #         aggregated_data[uid] = {(cid, ctype)}  # 如果uid不存在，创建一个新的集合
    #
    # # 打印聚合结果
    # for uid, combinations in aggregated_data.items():
    #     print(f"UID: {uid}")
    #     for cid, ctype in combinations:
    #         print(f"  ID: {cid}, Type: {ctype}")

def insert_browsinghistory():
    try:
        # 建立数据库连接
        conn = mysql.connector.connect(**config)
        cursor = conn.cursor()

        # 定义一个函数用于随机选择记录

        def random_pids_for_cid(cid, limit):
            select_pids_query = """
            SELECT pid,fatherid
            FROM posts as p
            JOIN categorize c ON p.categorizeid = c.cid
            WHERE fatherid = %s
            ORDER BY RAND()
            LIMIT %s
            """
            cursor.execute(select_pids_query, (cid, limit))
            pids = cursor.fetchall()
            print(pids)
            print([pid[0] for pid in pids])
            return [pid[0] for pid in pids]

        data_list = get_user_categories()
        # 循环处理每一条数据
        for uid, cid, type in data_list:
            print(uid, cid, type)
            # 随机选择1-20条记录的pid
            limit = random.randint(1, 20)
            pids = random_pids_for_cid(cid, limit)

            # 插入到browsinghistory表中
            for pid in pids:
                insert_history_query = """
                INSERT INTO browsinghistory (uid, pid)
                VALUES (%s, %s)
                """
                cursor.execute(insert_history_query, (uid, pid))

            # 提交事务
        conn.commit()

        print("Insertion into browsinghistory table completed.")

    except mysql.connector.Error as err:
        print(f"Error: {err}")

    finally:
        # 关闭数据库连接
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()
            print("MySQL connection is closed")


def insert_likes_favorite_follow():
    def get_browsing_history(cursor):
        try:
            select_browsing_query = """
            SELECT uid, pid
            FROM browsinghistory
            """
            cursor.execute(select_browsing_query)
            return cursor.fetchall()
        except mysql.connector.Error as e:
            print(f"Error selecting browsing history: {e}")
            return []

    def insert_likes(cursor, conn, uid, pids):
        try:
            for pid in pids:
                insert_like_query = """
                INSERT INTO `like` (uid, pid)
                VALUES (%s, %s)
                """
                cursor.execute(insert_like_query, (uid, pid))

                # 获取对应的 suid
                select_suid_query = """
                SELECT uid
                FROM posts
                WHERE pid = %s
                """
                cursor.execute(select_suid_query, (pid,))
                result = cursor.fetchone()
                if result:
                    suid = result[0]

                    # 更新 like 表中的 suid 字段
                    update_like_query = """
                    UPDATE `like`
                    SET suid = %s
                    WHERE uid = %s AND pid = %s
                    """
                    cursor.execute(update_like_query, (suid, uid, pid))

            # 提交事务
            conn.commit()

        except mysql.connector.Error as e:
            print(f"Error inserting into like table: {e}")
    def insert_likes_follow():
        # 连接数据库
        conn, cursor = connect_to_database(config)
        if not conn or not cursor:
            print("MySQL connection is closed")
            return

        try:
            # 获取浏览历史数据
            browsing_history = get_browsing_history(cursor)
            print(browsing_history)

            # 聚合浏览历史数据
            user_browsing = {}
            for uid, pid in browsing_history:
                if uid not in user_browsing:
                    user_browsing[uid] = []
                user_browsing[uid].append(pid)

            # 对每个用户进行处理
            for uid, pids in user_browsing.items():
                print(uid, pids)
                # 随机选择60%的数据
                sample_size = int(0.8 * len(pids))
                sample_pids = random.sample(pids, sample_size)

                # 插入到 like 表中并更新 suid
                insert_likes(cursor, conn, uid, sample_pids)

            print("Insertion into like table completed and suid updated.")

        except mysql.connector.Error as err:
            print(f"Error: {err}")

        finally:
            # 关闭数据库连接
            if conn.is_connected():
                cursor.close()
                conn.close()
                print("MySQL connection is closed")

    def get_like_data(cursor):
        try:
            select_like_query = """
            SELECT uid, pid
            FROM `like`
            """
            cursor.execute(select_like_query)
            return cursor.fetchall()
        except mysql.connector.Error as e:
            print(f"Error selecting like data: {e}")
            return []

    def insert_collections(cursor, conn, uid, pids):
        try:
            for pid in pids:
                insert_collection_query = """
                INSERT INTO collection (uid, pid)
                VALUES (%s, %s)
                """
                cursor.execute(insert_collection_query, (uid, pid))

            # 提交事务
            conn.commit()

        except mysql.connector.Error as e:
            print(f"Error inserting into collection table: {e}")

    def insert_favorite():
        # 连接数据库
        conn, cursor = connect_to_database(config)
        if not conn or not cursor:
            return

        try:
            # 获取 like 表数据
            like_data = get_like_data(cursor)

            # 聚合 like 数据
            user_likes = {}
            for uid, pid in like_data:
                if uid not in user_likes:
                    user_likes[uid] = []
                user_likes[uid].append(pid)

            # 对每个用户进行处理
            for uid, pids in user_likes.items():
                # 随机选择60%的数据
                sample_size = int(0.6 * len(pids))
                sample_pids = random.sample(pids, sample_size)

                # 插入到 collection 表中
                insert_collections(cursor, conn, uid, sample_pids)

            print("Insertion into collection table completed.")

        except mysql.connector.Error as err:
            print(f"Error: {err}")

        finally:
            # 关闭数据库连接
            if conn.is_connected():
                cursor.close()
                conn.close()
                print("MySQL connection is closed")

    # insert_favorite(
    def insert_follow(cursor, conn, fans, uid):
        try:
            insert_follow_query = """
            INSERT INTO follow (fans, uid)
            VALUES (%s, %s)
            """
            cursor.execute(insert_follow_query, (fans, uid))
            conn.commit()

        except mysql.connector.Error as e:
            print(f"Error inserting into follow table: {e}")

    def insert_follow_data():
        # 连接数据库
        conn, cursor = connect_to_database(config)
        if not conn or not cursor:
            return

        try:
            # 获取 like 表数据
            like_data = get_like_data(cursor)

            # 聚合 like 数据
            user_likes = {}
            for uid, suid in like_data:
                if uid not in user_likes:
                    user_likes[uid] = []
                user_likes[uid].append(suid)

            # 对每个用户进行处理
            for uid, suids in user_likes.items():
                # 随机选择30%的数据
                sample_size = max(1, int(0.3 * len(suids)))
                sample_suids = random.sample(suids, sample_size)

                # 插入到 follow 表中
                for suid in sample_suids:
                    insert_follow(cursor, conn, uid, suid)

            print("Insertion into follow table completed.")

        except mysql.connector.Error as err:
            print(f"Error: {err}")

        finally:
            # 关闭数据库连接
            if conn.is_connected():
                cursor.close()
                conn.close()
                print("MySQL connection is closed")

    # 示例调用
    insert_follow_data()


def insert_userLog():
    # def get_browsing_history_data(cursor):
    # def get_likes_data(cursor):
    def get_favorite(cursor):

        try:
            select_query = """
            SELECT bh.uid, bh.pid, fc.type
            FROM `like` AS bh
            JOIN posts AS p ON bh.pid = p.pid
            JOIN categorize AS c ON p.categorizeid = c.cid
            LEFT JOIN categorize AS fc ON c.fatherid = fc.cid
            """
            cursor.execute(select_query)
            return cursor.fetchall()
        except mysql.connector.Error as e:
            print(f"Error selecting browsing history data: {e}")
            return []

    def insert_userlog_data(cursor, conn, data):
        try:
            insert_query = """
            INSERT INTO userlog (userid, pid, action, category_name)
            VALUES (%s, %s, %s, %s)
            """
            for record in data:
                uid, pid, category_name = record
                cursor.execute(insert_query, (uid, pid, '收藏', category_name))
            conn.commit()
            print("Insertion into userlog table completed.")
        except mysql.connector.Error as e:
            print(f"Error inserting into userlog table: {e}")

    # 连接数据库
    conn, cursor = connect_to_database(config)
    if not conn or not cursor:
        return

    try:
        # 获取浏览历史数据
        # history_data = get_browsing_history_data(cursor)
        # 获取点赞数据
        # likes_data = get_likes_data(cursor
        favorite_data = get_favorite(cursor)

        # 插入到userlog表中
        insert_userlog_data(cursor, conn, favorite_data)

    except mysql.connector.Error as err:
        print(f"Error: {err}")

    finally:
        # 关闭数据库连接
        if conn.is_connected():
            cursor.close()
            conn.close()
            print("MySQL connection is closed")

if __name__ == '__main__':
    # userInfo_make()
    # recombine_categories()
    # get_user_categories()
    # insert_browsinghistory()
    # insert_likes_favorite_follow()
    insert_userLog()